
import logging
import datetime
import sqlite3
from flask import Flask, render_template, request, redirect, url_for, session, flash, jsonify

from db_methods.pull_senser_db import download_file_from_server

from index_data import IndexData
from sensor_info_table_methods import SensorInfoTableMethods

from xlsx_methods import XlsxMethods
from settings import *

app = Flask(__name__)

# 设置一个唯一的、保密的 SECRET_KEY
app.secret_key = 'your_secret_key_here'


@app.route('/')
def index():
    # end_time = request.args.get('start_time', (datetime.datetime.now() - datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S"), type=str).replace("T", " ")
    # start_time = request.args.get('end_time', (datetime.datetime.now() - datetime.timedelta(hours=12)).strftime("%Y-%m-%d %H:%M:%S"), type=str).replace("T", " ")

    end_time = request.args.get('end_time')
    start_time = request.args.get('start_time')

    print(start_time, end_time)


    if not end_time:
        end_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    else:
        end_time = end_time.replace("T", " ")
        end_time = end_time + ":00"
    if not start_time:
        start_time = (datetime.datetime.now() - datetime.timedelta(hours=4)).strftime("%Y-%m-%d %H:%M:%S")
    else:
        start_time = start_time.replace("T", " ")
        start_time = start_time + ":00"
    print(start_time, end_time)

    # try:

    #     index_data = IndexData().get_index_data(start_time=start_time, end_time=end_time)

    # except Exception as e:
    #     logging.error(e)
    #     index_data = {}
    try:

        index_data = IndexData().get_index_data(start_time=start_time, end_time=end_time)
    # 处理数据库异常: sqlite3.DatabaseError: database disk image is malformed
    except sqlite3.DatabaseError as e:

        logging.error(e)
        index_data = {}
    
    
    return render_template('index.html.j2', index_data=index_data, start_time=start_time, end_time=end_time)


@app.route('/about')
def about():
    return render_template('about.html')


@app.route("/get_master_station_sensor_data", methods=["POST"])
def get_master_station_sensor_data():

    download_status = download_file_from_server(hostname=MASTER_STATION_HOSTNAME, 
                                                username=MASTER_STATION_USERNAME, 
                                                password=MASTER_STATION_PASSWORD, 
                                                remote_file_path=MASTER_STATION_SENSOR_DATA_FILE_PATH, 
                                                local_file_path=LOCAL_FILE_PATH,
                                                port=MASTER_STATION_SSH_PORT)
    
    if download_status[0]:
        logging.debug("Master Station Sensor Data Downloaded Successfully")
        return jsonify({
            "status": 1,
            "errorCode": None,
            "errorMsg": None,
            "resultBody": "Master Station Sensor Data Downloaded Successfully"
        })

    else:
        logging.error(download_status[1])
        return jsonify({
            "status": -1,
            "errorCode": 1000,
            "errorMsg": download_status[1],
            "resultBody": None
        })

@app.route('/ws')
def ws():
    return render_template('ws.html')



@app.route('/ws_')
def ws_():

    xlsx_info = XlsxMethods().get_info()
    print(xlsx_info)
    return render_template('ws_.html.j2', xlsx_info=xlsx_info)

@app.route('/test')
def test():

    return render_template('test.html')

@app.route('/sensor_data/<sensor_mac>')
def sensor_data(sensor_mac):

    start_time = request.args.get('start_time')
    end_time = request.args.get('end_time')

    if not end_time:
        end_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    else:
        end_time = end_time.replace("T", " ")
        end_time = end_time + ":00"
    if not start_time:
        start_time = (datetime.datetime.now() - datetime.timedelta(hours=4)).strftime("%Y-%m-%d %H:%M:%S")
    else:
        start_time = start_time.replace("T", " ")
        start_time = start_time + ":00"
    print(start_time, end_time)

    sensor_mac = sensor_mac.lower()
    with SensorInfoTableMethods() as db:

        sensor_data = db.select_sensor_data(sensor_mac, start_time=start_time, end_time=end_time)

    # sensor_data output example: [(213588, '010b', '倾角', 'e51c038800010b00246303071400000000000c000463000101004ace', '2024-08-10 07:10:53', None), (213701, '010b', '倾角', 'e51c038900010b00246303071400000000000c000463000101008752', '2024-08-10 07:13:04', None)]
    
    return render_template('sensor_data.html.j2', sensor_data=sensor_data, start_time=start_time, end_time=end_time, sensor_mac=sensor_mac)


if __name__ == '__main__':

    # from mqtt_service import run_mqtt_service_in_thread

    # # 启动 MQTT 服务
    # run_mqtt_service_in_thread()

    app.run(debug=True, host='0.0.0.0', port=5000)


    # my_flask_app/
    # app.py
    # templates/
    #     index.html
    #     about.html
    # static/
    #     css/
    #         style.css
    #     js/
    #         script.js
    #     images/
